#-*- coding = utf-8 -*-
#@Time: 2020/4/11 10:25
#@Author: mx
#@File: loupan_to_db.py
#@Software: IntelliJ IDEA

import os
import pymysql
from lib.utility.path import DATA_PATH
from lib.zone.city import *
import records
from lib.spider.base_spider import *

pymysql.install_as_MySQLdb()

class LouPanToMysql(BaseSpider):

    def loupan_data_to_db(self,city):
        logger.info("loupan to mysql start...")
        database = "mysql"
        db = None
        db = records.Database('mysql://root:sh@88861158@47.108.31.200:3308/house?charset=utf8', encoding='utf-8')
        #准备城市,日期
        # date = "20180331"   # 指定采集数据的日期
        # city = "sh"         # 指定采集数据的城市
        date = get_date_string()
        city_ch = get_chinese_city(city)
        csv_dir = "{0}/{1}/loupan/{2}/{3}".format(DATA_PATH,SPIDER_NAME,city,date)

        files = list()
        if not os.path.exists(csv_dir):
            logger.info("{0} does not exist.".format(csv_dir))
            logger.info("Please run 'python xiaoqu.py' firstly.")
            logger.info("Bye.")
            exit(0)
        else:
            logger.info('OK, start to process ' + get_chinese_city(city))
        for csv in os.listdir(csv_dir):
            data_csv = csv_dir + "/" + csv
            # print(data_csv)
            files.append(data_csv)

        # 清理数据
        count = 0
        row = 0
        col = 0
        for csv in files:
            with open(csv,'r') as f:
                for line in f:
                    count += 1
                    text = line.strip()
                    try:
                        # 如果小区名里面没有逗号，那么总共是4项
                        if text.count(',') == 3:
                            date, loupan, price, total = text.split(',')
                        elif text.count(',') < 3:
                            continue
                        else:
                            fields = text.split(',')
                            date = fields[0]
                            loupan = fields[1]
                            price = fields[2]
                            total = fields[3]
                    except Exception as e:
                        logger.error(text)
                        logger.error(e)
                        continue
                    total = total.replace(r'万/套','')
                    price = price.replace(r'价格待定','0')
                    total = float(total)
                    price = int(price)
                    # logger.info("{0} {1} {2} {3}".format(date,loupan,price,total))
                    #写入数据库
                    db.query('INSERT INTO loupan(city,loupan,price,total,date) '
                             'VALUES(:city, :loupan, :price, :total, :date)',
                             city=city_ch,loupan=loupan,price=price,total=total,date=date)
        logger.info("Total write {0} items to database.".format(count))
        logger.info("loupan to mysql end...")


if __name__ == "__main__":
    pass